<%@page import="com.mde.util.Validator"%>
<%@page import="java.io.File"%>
<%@page import="java.util.Scanner"%>
<%@ page import="java.sql.*" %>

<%@ page contentType="text/html;charset=UTF-8" %>
<%@ page errorPage="error.jsp" %>

<html>
<head>
	<title>MySQL Database creation</title>
</head>
<body>
<%!	
	Statement stmt;
	Connection con;
	String url = Validator.dbURL;	
	String POINTS_TABLE_CREATE = 
			"CREATE TABLE IF NOT EXISTS Points" + 
			"(" + 
			"id int NOT NULL PRIMARY KEY AUTO_INCREMENT, " +
			"latitude double, " + 
			"longitude double, " + 
			"type text, " + 
			"name text" + 
			")";
	
	String EDGES_TABLE_CREATE = 
			"CREATE TABLE IF NOT EXISTS Edges" + 
			"(" + 
			"id int NOT NULL PRIMARY KEY AUTO_INCREMENT, " +
			"fromId int, " + 
			"whereId int, " +
			"routeId int, " + 
			"weight double " + 
			")";
	
	String ROUTES_TABLE_CREATE = 
			"CREATE TABLE IF NOT EXISTS Routes" + 
			"(" + 
			"id int NOT NULL PRIMARY KEY AUTO_INCREMENT, " +
			"routeId int, " +
			"edgeId int, " + 
			"edgeOrder int, " + 
			"type text, " +
			"name text " +	
			")";
	
	String DEFAULT_EDGES_INSERT = "INSERT INTO Edges (fromId, whereId, routeId, weight) " +
			"VALUES (0, 0, 0, 0)";
	
	String INSERT_POINT = "INSERT INTO Points (latitude, longitude, type, name) ";
	
	String busStop = "bus_stop";
	
	String stationsInitFilePath = "/home/emergency/MDE/STSprojects/ParseXML/stations.txt";
	
	String SELECT_FROM_POINTS = "SELECT id, latitude, longitude, type, name FROM Points;";
	
	String TABLE_START = "<table>" + 
			"	<thead>" +
			"		<tr>" +
			"			<td>id</td>" +
			"			<td>name</td>" +
			"			<td>type</td>" +
			"			<td>latitude</td>" +
			"			<td>longitude</td>" +
			"		</tr>" +
			"	</thead>" + 
			"	<tbody>";
%>
	
<jsp:scriptlet><![CDATA[
	Class.forName("com.mysql.jdbc.Driver");
	con = DriverManager.getConnection(url, Validator.dbUsername, Validator.dbUserpass); 
	stmt = con.createStatement();	
	stmt.execute("DROP TABLE IF EXISTS Points;");
	stmt.execute("DROP TABLE IF EXISTS Routes;");
	stmt.execute("DROP TABLE IF EXISTS Edges;");
	stmt.execute(ROUTES_TABLE_CREATE);
	stmt.execute(POINTS_TABLE_CREATE);
	stmt.execute(EDGES_TABLE_CREATE);
	stmt.execute(DEFAULT_EDGES_INSERT);

	Scanner scan = new Scanner(new File(stationsInitFilePath), "utf8");

	while (scan.hasNext()) {
		double lat = scan.nextDouble(), lon = scan.nextDouble();
		String name = scan.nextLine().trim();
		stmt.execute(
				INSERT_POINT + 
				"VALUES (" + 
							lat + ", " + 
							lon + ", \"" +		
							busStop + "\", \"" + 
							name + 			
						"\");"
			);
	}
	
	scan.close();

	String query = SELECT_FROM_POINTS;
	ResultSet rs = stmt.executeQuery(query);

	out.println(TABLE_START);

	while(rs.next()){
         //Retrieve by column name
         int id  = rs.getInt("id");
         String pointName = rs.getString("name");
         String pointType = rs.getString("type");
         double latitude = rs.getDouble("latitude");
         double longitude = rs.getDouble("longitude");

         //Display values
         out.println(
        		 	"<tr>" +
        		 	"<td>" + id + "</td>" + 
      		 			"<td>" + pointName + "</td>" + 
      		 			"<td>" + pointType + "</td>" + 
      		 			"<td>" + latitude + "</td>" + 
      		 			"<td>" + longitude + "</td>" +
      		 			"</tr>"
         );
      }
	
	out.println("</tbody></table>");
	
	stmt.close();
	con.close();
]]></jsp:scriptlet>
</body>
</html>